﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySQLStudio.DataAccess;
using MySQLStudio.Common;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Data;

namespace MySQLStudio.BusinessLogic
{
    public class SqlExecutionController
    {
        public static SqlExecutionResult ExecuteSqlStatement(SqlExecutionContext executionContext)
        {
            MySqlDataReader reader = null;
            DataTable schemaTable = null;
            DataColumn resultDataColumn = null;

            // create execution result object
            SqlExecutionResult executionResult = new SqlExecutionResult();

            try
            {
                if (executionContext != null)
                   reader = MySqlDataAccess.ExecuteDataReader(executionContext.ConnectionString,
                                                              executionContext.SqlStatement);
                
                //Retrieve column schema into a DataTable.
                schemaTable = reader.GetSchemaTable();

                if (schemaTable != null)
                {
                    executionResult.DataRead = true;

                    //For each field in the table...
                    foreach (DataRow resultField in schemaTable.Rows)
                    {
                        string columnName = string.Empty;
                        string columnType = string.Empty;

                        //For each property of the field...
                        foreach (DataColumn property in schemaTable.Columns)
                        {
                            switch (property.ColumnName)
                            {
                                case "ColumnName":
                                    columnName = resultField[property.ColumnName].ToString();
                                    break;
                                case "DataType":
                                    columnType = resultField[property.ColumnName].ToString();
                                    break;
                            }
                        }


                        resultDataColumn = new DataColumn(columnName, Type.GetType(columnType));
                        executionResult.AddColumn(resultDataColumn);
                    }
                }


                // extract data
                executionResult.ReadData(reader);
                executionResult.AddMessage(string.Format("{0} row(s) returned", executionResult.NumberOfRowsRead));
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                {
                    MySql.Data.MySqlClient.MySqlException mysqlEx = ex.InnerException as MySql.Data.MySqlClient.MySqlException;
                    if (mysqlEx != null)                       
                        executionResult.AddMessage(string.Format("Error Code - {0}, {1}", mysqlEx.ErrorCode, mysqlEx.Message));
                    else
                        executionResult.AddMessage(ex.InnerException.Message);
                }
                else
                    executionResult.AddMessage(ex.Message);
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                }
            }

            return executionResult;
        }

        
    }
}
